package dao;

import dto.ObraSocialDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class ObraSocialDAO {

  public static final String Tab =
    "create table obra_social (" +
    "id serial," + //pk
    "nombre text," +
    "contacto_nombre text," +
    "contacto_apellido text," +
    "contacto_telefono text," +
    "contacto_email text" +
    ");" +

    "alter table obra_social add constraint obra_social_pk primary key (id);"
  ;

  public static final String Tuples =
    "insert into obra_social (id, nombre) values" +
    "(0, 'Particular (sin obra social)');" ;

  /*
    "insert into obra_social (nombre,contacto_nombre,contacto_apellido,contacto_telefono,contacto_email) values" +
    "('PAMI', 'Guillermo', 'Vilas', '5383-6832', 'rub201409@gmail.com')," +
    "('OSDE', 'Roger', 'Federer', '15-9435-7859', 'kabokirch@gmail.com')," +
    "('OSPA', 'Andy', 'Roddick', '0800-321-6776', 'eze.aki123@gmail.com')," +
    "('OSPLAD', 'Andre', 'Agassi', '0810-666-7752', 'rub201409@gmail.com')," +
    "('OSTAMMA', 'David', 'Nalbandian', '0800-333-6373', 'eze.aki123@gmail.com')," +
    "('OSPACA', 'Gastón', 'Gaudio', '0800-999-6772', 'rub201409@gmail.com')," +
    "('O.S.P.S.I.P.', 'Juan Martín', 'del Potro', '4959-6500', 'kabokirch@gmail.com');"
  ;
  */

  private static ObraSocialDAO dao = null;
  private final Connection c;

  public static ObraSocialDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new ObraSocialDAO();
    }
    return dao;
  }

  private ObraSocialDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("obra_social");
  }

  public ArrayList<ObraSocialDTO> All() throws SQLException {
    return getObraSocialDTOs("select * from obra_social order by nombre;");
  }

  public ArrayList<ObraSocialDTO> SearchByNombre(String s) throws SQLException {
    return getObraSocialDTOs(queryByNombre(s));
  }

  private String queryByNombre(String name) throws SQLException {
    String query = String.format(
      "select * from obra_social where nombre = '%s' order by nombre;",
      name
    );
    return query;
  }

  private ArrayList<ObraSocialDTO> getObraSocialDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<ObraSocialDTO> dtos = new ArrayList<>();
    while (r.next()) {
      dtos.add(
        new ObraSocialDTO(
          r.getInt("id"),
          r.getString("nombre"),
          r.getString("contacto_nombre"),
          r.getString("contacto_apellido"),
          r.getString("contacto_telefono"),
          r.getString("contacto_email")
        )
      );
    }
    r.close();
    s.close();
    return dtos;
  }

  public ObraSocialDTO ObraSocialDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from obra_social where id = " + id
    );
    ObraSocialDTO dto = null;
    if (r.next()) {
      dto = new ObraSocialDTO(
        r.getInt("id"),
        r.getString("nombre"),
        r.getString("contacto_nombre"),
        r.getString("contacto_apellido"),
        r.getString("contacto_telefono"),
        r.getString("contacto_email")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  // Guarda una **nueva** obra social.
  public int Save(ObraSocialDTO o) throws SQLException {
    String query = String.format(
      "insert into obra_social values (default, '%s', '%s', '%s', '%s', '%s');",
      o.getNombre(), o.getContactoNombre(), o.getContactoApellido(), o.getContactoTelefono(), o.getContactoEmail()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void UpdateContacto(ObraSocialDTO o) throws SQLException {
    String query = String.format(
      "update obra_social set contacto_nombre = '%s', contacto_apellido = '%s', contacto_telefono = '%s', contacto_email = '%s' where id = %d;",
      o.getContactoNombre(), o.getContactoApellido(), o.getContactoTelefono(), o.getContactoEmail(), o.getId()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
  }

  public void Delete(int id) throws SQLException {
    Statement s = c.createStatement();
    s.executeUpdate("delete from obra_social where id = " + id);
    s.close();
  }

  // Modifica un atributo de tipo string en una obra social.
  public ObraSocialDTO EditString(int id, String attr, String str) throws SQLException {
    String query = String.format(
      "update obra_social set %s = '%s' where id = %d;",
      attr, str, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return ObraSocialDTO(id);
  }

}
